﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Net;
using System.Data;
using System.Data.SqlClient;
using Facebook;
using Twitterizer;


namespace EsaProjectRest.Service
{
    class Services
    {
        ServiceRest service1 = new ServiceRest();
        //Function that saves the access token of social networks
        public Boolean InsertToken(string id, string access_token, string service, string url)
        {
            //open connection
            service1.OpenConnection();
            //check if it is the first social network that is activated
            string verify = ReadService(id);

            if (verify == "0")
            {
                service1.OpenConnection();
                //insert access token into database (table service)
                string sql = string.Format("INSERT INTO [Service](Id," + service + ") values('{0}','{1}')", id, access_token);
                SqlCommand com = new SqlCommand(sql, service1.conn);
                com.ExecuteNonQuery();
                //close connection
                service1.CloseConnection();
                //function that saves the avatar of the social network enabled
                avatar(id, access_token, url, service);

                return true;
            }
            else
            {
                service1.OpenConnection();
                //update access token into database(table service)
                string sql = string.Format("UPDATE [Service] SET " + service + "='" + access_token + "' WHERE (Id = '" + id + "') ");
                SqlCommand com = new SqlCommand(sql, service1.conn);
                com.ExecuteNonQuery();
                //close connection
                service1.CloseConnection();
                //function that saves the avatar of the social network enabled
                avatars(id, access_token, url, service);

                return true;
            }

        }

        public string[] GetPublicPage(string id, string service)
        {
            string accessToken = ReadToken(id, service);
            string[] page = new string[10];
            
            if (service == "Facebook")
            {
                var client = new FacebookClient(accessToken);
                dynamic id_me = client.Get("me");
                string id_fb = id_me.id;
                
                dynamic me = client.Get(id_fb + "?fields=accounts.fields(name,access_token,category)");
                
                for (int i = 0; i < 10; i++)
                {
                    try
                    {
                        if(me.accounts.data[i].category != "Application")
                        {
                            page[i]= me.accounts.data[i].name+";"+me.accounts.data[i].access_token;
                        }                        
                    }
                    catch (Exception ex)
                    {
                        System.Diagnostics.Debug.Print("C: "+ex.Message);
                    }
                }
            }
            return page;
        }

        public Boolean InsertTokenPage(string id, string access_token, string service)
        {
            //open connection
            service1.OpenConnection();
            try
            {
                string sql = string.Format("UPDATE [Service] SET "+service+"='" +access_token+ "' WHERE (Id = '" + id + "') ");
                System.Diagnostics.Debug.Print(sql);
                SqlCommand com = new SqlCommand(sql, service1.conn);
                com.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                return false;
            }
        }

        //checks to see if the id is in the table service
        private string ReadService(string id)
        {
            //select from the database the access token of social networks
            DataSet vds = new DataSet();
            string vsql = string.Format("SELECT Id FROM [Service] WHERE (Id = '" + id + "') ");            
            SqlCommand vCom = new SqlCommand(vsql, service1.conn);
            SqlDataAdapter vda = new SqlDataAdapter(vCom);            
            vda.Fill(vds);

            try
            {
                //memorize id
                string id_user = vds.Tables[0].Rows[0]["Id"].ToString();
                vda.Dispose();
                
                service1.CloseConnection();
                return id;
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
                return "0";
            }
        }
        //Save the image of the first service activated
        private void avatar(string id, string token, string url, string service)
        {
            System.Console.WriteLine(url);
            try
            {
                service1.OpenConnection();
               
                if (service == "Facebook")
                {
                    //proceeds via http request uri of the avatar
                    WebRequest request = WebRequest.Create("" + url + token + "");
                    WebResponse responseFB = request.GetResponse();
                    //saves the image in the user table in the logo
                    string sql = string.Format("UPDATE [User] SET Logo='" + responseFB.ResponseUri.ToString() + "' WHERE (Id = '" + id + "') ");
                    SqlCommand com = new SqlCommand(sql, service1.conn);
                    com.ExecuteNonQuery();
                    //part of the table avatarSN the link to the service activated
                    string sql2 = string.Format("INSERT INTO [AvatarSN](Id," + service + ") values('{0}','{1}')", id, responseFB.ResponseUri.ToString());
                    SqlCommand com2 = new SqlCommand(sql2, service1.conn);
                    com2.ExecuteNonQuery();
                }
                if (service == "Twitter" || service == "Google")
                {
                    //saves the image in the user table in the logo
                    string sql = string.Format("UPDATE [User] SET Logo='" + url + "' WHERE (Id = '" + id + "') ");
                    SqlCommand com = new SqlCommand(sql, service1.conn);
                    com.ExecuteNonQuery();
                    //part of the table avatarSN the link to the service activated
                    string sql2 = string.Format("INSERT INTO [AvatarSN](Id," + service + ") values('{0}','{1}')", id, url);
                    SqlCommand com2 = new SqlCommand(sql2, service1.conn);
                    com2.ExecuteNonQuery();
                }
                
                service1.CloseConnection();
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
            }
        }

        //Save the image of the social network activated
        private void avatars(string id, string token, string url, string service)
        {
            System.Console.WriteLine(url);
            try
            {
                service1.OpenConnection();
                if (service == "Facebook")
                {
                    //proceeds via http request uri of the avatar
                    WebRequest request = WebRequest.Create("" + url + token + "");
                    WebResponse responseFB = request.GetResponse();
                    //saves the image in the avatarSN table in the Facebook
                    string sql = string.Format("UPDATE [AvatarSN] SET Facebook='" + responseFB.ResponseUri.ToString() + "' WHERE (Id = '" + id + "') ");
                    SqlCommand com = new SqlCommand(sql, service1.conn);
                    com.ExecuteNonQuery();
                }
                if (service == "Twitter")
                {
                    //saves the image in the avatarSN table in the Twitter
                    string sql = string.Format("UPDATE [AvatarSN] SET Twitter='" + url + "' WHERE (Id = '" + id + "') ");
                    SqlCommand com = new SqlCommand(sql, service1.conn);
                    com.ExecuteNonQuery();
                }

                if (service == "Google")
                {
                    //saves the image in the avatarSN table in the Google
                    string sql = string.Format("UPDATE [AvatarSN] SET Google='" + url + "' WHERE (Id = '" + id + "') ");
                    SqlCommand com = new SqlCommand(sql, service1.conn);
                    com.ExecuteNonQuery();
                }

                service1.CloseConnection();
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
            }
        }

        public Boolean DeleteToken(string id, string service)
        {
            //open connection
            try
            {
                service1.OpenConnection();
                if (service == "Facebook")
                {                    
                    //delete the access token in the service table in the Facebook
                    string sql = string.Format("UPDATE [Service] SET Facebook='' WHERE (Id = '" + id + "') ");
                    SqlCommand com = new SqlCommand(sql, service1.conn);
                    com.ExecuteNonQuery();
                    string sql2 = string.Format("UPDATE [AvatarSN] SET Facebook='' WHERE (Id = '" + id + "') ");
                    SqlCommand com2 = new SqlCommand(sql2, service1.conn);
                    com2.ExecuteNonQuery();
                }
                if (service == "Twitter")
                {
                    //delete the access token in the service table in the Twitter
                    string sql = string.Format("UPDATE [Service] SET Twitter='' WHERE (Id = '" + id + "') ");
                    SqlCommand com = new SqlCommand(sql, service1.conn);
                    com.ExecuteNonQuery();
                    string sql2 = string.Format("UPDATE [AvatarSN] SET Twitter='' WHERE (Id = '" + id + "') ");
                    SqlCommand com2 = new SqlCommand(sql2, service1.conn);
                    com2.ExecuteNonQuery();
                }

                if (service == "Google")
                {
                    //delete the access token in the service table in the Google
                    string sql = string.Format("UPDATE [Service] SET Google='' WHERE (Id = '" + id + "') ");
                    SqlCommand com = new SqlCommand(sql, service1.conn);
                    com.ExecuteNonQuery();
                    string sql2 = string.Format("UPDATE [AvatarSN] SET Google='' WHERE (Id = '" + id + "') ");
                    SqlCommand com2 = new SqlCommand(sql2, service1.conn);
                    com2.ExecuteNonQuery();
                }                
                service1.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
                return false;
            }

        }

        public string ReadToken(string id, string service)
        {
            //select from the database the access token of social networks
            DataSet vds = new DataSet();
            string vsql = string.Format("SELECT " + service + " FROM [Service] WHERE (Id = '" + id + "') ");

            SqlCommand vCom = new SqlCommand(vsql, service1.conn);
            SqlDataAdapter vda = new SqlDataAdapter(vCom);
            vda.Fill(vds);

            try
            {
                //memorize id
                string token = vds.Tables[0].Rows[0][service].ToString();
                vda.Dispose();

                service1.CloseConnection();
                return token;
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
                return "0";
            }
        }

        public string[] GetMyPosts(string id,string where, string start_offset, string end_offset)
        {
            ServiceRest service1 = new ServiceRest();
            service1.OpenConnection();
            try{                    
                /*DataSet vds = new DataSet();
                string vsql = string.Format("SELECT COUNT(Id) AS Tot FROM  [Post] WHERE Id_user='"+id+"' "+where+" ");
                //System.Diagnostics.Debug.Print(vsql);
                SqlCommand vCom = new SqlCommand(vsql, service1.conn);
                SqlDataAdapter vda = new SqlDataAdapter(vCom);
                vda.Fill(vds);
                string rows = vds.Tables[0].Rows[0]["Tot"].ToString();
                int tot_rows = Int32.Parse(rows);                
                */
                int startoffset = int.Parse(start_offset);
                int endoffset = int.Parse(end_offset);
                string[] text = new string[16];
                DataSet vds2 = new DataSet();
                string sql = string.Format("SELECT TOP(" + endoffset + ") Id_user,Message,Date,Picture,SocialNetwork,N_Likes,N_Comments,N_Shares FROM [Post] WHERE Id_user='" + id + "' " + where + " ORDER BY Date DESC");
                //string sql = string.Format("SELECT Id_user,Message, Date,Picture,SocialNetwork FROM (SELECT TOP (10) Id_user,Message, Date, Picture,SocialNetwork FROM (SELECT TOP (" + offset + ") Id_user,Message, Date,Picture, SocialNetwork FROM Post ORDER BY Date DESC) AS Post_one ORDER BY Date) AS Post_two WHERE Id_user='" + id + "' " + where + " ORDER BY Date DESC");
                System.Diagnostics.Debug.Print("SQL: "+sql);
                SqlCommand vCom2 = new SqlCommand(sql, service1.conn);                
                SqlDataAdapter vda2 = new SqlDataAdapter(vCom2);
                vda2.Fill(vds2);
                int j = 0;
                DateTime time;
                
                for (int i = startoffset; i < endoffset; i++)
                {
                    try
                    {
                        //System.Diagnostics.Debug.Print("A: " + vds2.Tables[0].Rows[i]["Message"].ToString());
                        if (vds2.Tables[0].Rows[i]["Message"] != null)
                        {
                            time = Convert.ToDateTime(vds2.Tables[0].Rows[i]["Date"]);
                            time = time.AddHours(2);
                            text[j] = vds2.Tables[0].Rows[i]["Message"].ToString() + "+-+" + time.ToString() + "+-+" + vds2.Tables[0].Rows[i]["SocialNetwork"].ToString() + "+-+" + vds2.Tables[0].Rows[i]["Picture"].ToString() + "+-+" + vds2.Tables[0].Rows[i]["N_Likes"].ToString() + "+-+" + vds2.Tables[0].Rows[i]["N_Comments"].ToString() + "+-+" + vds2.Tables[0].Rows[i]["N_Comments"].ToString();
                            text[j] = HttpUtility.HtmlDecode(text[j]);
                            j++;
                        }
                        else
                        {
                            i = endoffset + 1;
                        }
                    }
                    catch (Exception ex)
                    {
                        System.Console.WriteLine(ex.Message);
                        i = endoffset + 1;
                    }
                }
                
                service1.CloseConnection();

                return text;
            }catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
                return null;
            }
            
            
        }

        public string[] GetMyTags(string id, string where, string start_offset, string end_offset)
        {
            ServiceRest service1 = new ServiceRest();
            service1.OpenConnection();
            try
            {                
                int startoffset = int.Parse(start_offset);
                int endoffset = int.Parse(end_offset);
                string[] text = new string[16];
                DataSet vds2 = new DataSet();
                string sql = string.Format("SELECT TOP(" + endoffset + ") Id_user,Tag,Message,Date,Picture,SocialNetwork,Img_Tag FROM [TagPost] WHERE Id_user='" + id + "' " + where + " ORDER BY Date DESC");
                //string sql = string.Format("SELECT Id_user,Message, Date,Picture,SocialNetwork FROM (SELECT TOP (10) Id_user,Message, Date, Picture,SocialNetwork FROM (SELECT TOP (" + offset + ") Id_user,Message, Date,Picture, SocialNetwork FROM Post ORDER BY Date DESC) AS Post_one ORDER BY Date) AS Post_two WHERE Id_user='" + id + "' " + where + " ORDER BY Date DESC");
                System.Diagnostics.Debug.Print("SQL: " + sql);
                SqlCommand vCom2 = new SqlCommand(sql, service1.conn);
                SqlDataAdapter vda2 = new SqlDataAdapter(vCom2);
                vda2.Fill(vds2);
                int j = 0;
                DateTime time;

                for (int i = startoffset; i < endoffset; i++)
                {
                    try
                    {
                        System.Diagnostics.Debug.Print("A: " + vds2.Tables[0].Rows[i]["Message"].ToString());
                        if (vds2.Tables[0].Rows[i]["Message"] != null)
                        {
                            time = Convert.ToDateTime(vds2.Tables[0].Rows[i]["Date"]);
                            time = time.AddHours(2);
                            text[j] = vds2.Tables[0].Rows[i]["Tag"].ToString() + "+-+" + vds2.Tables[0].Rows[i]["Img_Tag"].ToString() + "+-+" + vds2.Tables[0].Rows[i]["Message"].ToString() + "+-+" + time.ToString() + "+-+" + vds2.Tables[0].Rows[i]["SocialNetwork"].ToString() + "+-+" + vds2.Tables[0].Rows[i]["Picture"].ToString();
                            text[j] = HttpUtility.HtmlDecode(text[j]);
                            j++;
                        }
                        else
                        {
                            i = endoffset + 1;
                        }
                    }
                    catch (Exception ex)
                    {
                        System.Console.WriteLine(ex.Message);
                        i = endoffset + 1;
                    }
                }

                service1.CloseConnection();

                return text;
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
                return null;
            }
        }
 
        public Boolean DeletePost(string id, string service)
        {
            ServiceRest service1 = new ServiceRest();
            service1.OpenConnection();
            try
            {
                string sql = string.Format("DELETE FROM [Post] WHERE (id_user='" + id + "' AND SocialNetwork='" + service + "')");
                SqlCommand com = new SqlCommand(sql, service1.conn);
                com.ExecuteNonQuery();
                string sql2 = string.Format("DELETE FROM [TagPost] WHERE (id_user='" + id + "' AND SocialNetwork='" + service + "')");
                SqlCommand com2 = new SqlCommand(sql2, service1.conn);
                com2.ExecuteNonQuery();

                return true;
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
                return false;
            }
        }

        public Boolean N_Liker(string id,string service)
        {
            service1.OpenConnection();
            //check if it is the first social network that is activated
            
            int liker = 0;
            char[] delimiter = { ';' };
            string myAccessToken = ReadToken(id, service);
            if (service == "Facebook")
            {
                try
                {
                    FacebookClient client = new FacebookClient(myAccessToken);

                    dynamic id_me = client.Get("me");
                    //string id_fb = id_me.id;

                    //dynamic me = client.Get(id_fb);
                    string id_friend = id_me.likes.ToString();
                    liker = int.Parse(id_friend);
                }
                catch (Exception ex)
                {
                    System.Console.WriteLine(ex.Message);                   
                }
            }
            if (service == "Twitter")
            {
                try
                {
                    string[] TokenSplit = myAccessToken.Split(delimiter);
                    string accessToken = TokenSplit[0];
                    string accessTokenSecret = TokenSplit[1];
                    OAuthTokens tokens = new OAuthTokens();

                    tokens.ConsumerKey = "NxCOfv9GUBLxXCGaAgYQQ";
                    tokens.ConsumerSecret = "lLt60LIinqbm2LAhxNpxUdAwOoO1AJu3xXdcSuFQXRo";
                    tokens.AccessToken = accessToken;
                    tokens.AccessTokenSecret = accessTokenSecret;

                    TwitterResponse<TwitterUserCollection> myFollowers = TwitterFriendship.Followers(tokens);

                    liker = myFollowers.ResponseObject.Count;
                }
                catch (Exception ex)
                {
                    System.Console.WriteLine(ex.Message);
                    return false;
                }
                
            }
            if (service == "Google")
            {
                liker = 0;
            }
            try
            {
                service1.OpenConnection();
                DataSet vds = new DataSet();
                string vsql = string.Format("SELECT Id FROM [Liker] WHERE (Id = '" + id + "') ");

                SqlCommand vCom = new SqlCommand(vsql, service1.conn);
                SqlDataAdapter vda = new SqlDataAdapter(vCom);
                vda.Fill(vds);
                string id_user = vds.Tables[0].Rows[0]["Id"].ToString();
                System.Diagnostics.Debug.Print("ID: "+id_user);
                vda.Dispose();

                string sql = string.Format("UPDATE [Liker] SET " + service + "='" + liker + "' WHERE (Id = '" + id + "') ");
                System.Diagnostics.Debug.Print(sql);
                SqlCommand com = new SqlCommand(sql, service1.conn);
                com.ExecuteNonQuery();
                //close connection
                service1.CloseConnection();                
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
                service1.OpenConnection();
                //insert access token into database (table service)
                string sql = string.Format("INSERT INTO [Liker](Id," + service + ") values('{0}','{1}')", id, liker);
                System.Diagnostics.Debug.Print(sql);
                SqlCommand com = new SqlCommand(sql, service1.conn);
                com.ExecuteNonQuery();
                //close connection
                service1.CloseConnection();
            }
            return true;             
        }
    }
}

